/*
 * Copyright (c) 2019 Ruijie Networks, Inc. and others. All rights reserved.
 */
package com.ruoyi.common.utils;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.support.cglib.core.CollectionUtils;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

/**
 * excel文件处理
 *
 * @author yangshumu@ruijie.com.cn
 * @date 2019/09/23
 */

public class ExcelUtil1 {

    private final static Logger log = LoggerFactory.getLogger(ExcelUtil1.class);
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";
    
    private final static int MAX_MERGE_VALUE = 2;

    public static int RED = IndexedColors.RED.index;
    public static int BLUE = IndexedColors.BLUE.index;
    
    
	/**浏览器下载data数据
	 * @param filePath 文件名
	 * @param header 表格头
	 * @param dataList 数据列表
	 * @param response 响应流
	 * @param isMerge 是否合并单元格
	 * @throws Exception
	 */
    public static <T> void exportWorkBookData(String filePath, Map<String, String> header, List<T> dataList,
			HttpServletResponse response) throws Exception{
    	exportWorkBookData(filePath, header, dataList,response, false);
    }
    
	public static <T> void exportWorkBookData(String filePath, Map<String, String> header, List<T> dataList,
			HttpServletResponse response,boolean isMerge) throws Exception {
		FileInputStream inputStream = null;
		OutputStream outputStream = null;
		try {
			filePath = filePath.replaceAll("\\\\", "/");
			String exportFileName = filePath;
			if (filePath.lastIndexOf("/") > 0) {
				exportFileName = filePath.substring(filePath.lastIndexOf("/")+1);
			}
			byte[] dataByte = exportWorkBookData(filePath, header, dataList, isMerge);
			// 让浏览器用utf8来解析返回的数据
			response.setHeader("Content-type", "text/html;charset=UTF-8");
			// servlet用UTF-8转码，而不是用默认的ISO8859
			response.setCharacterEncoding("UTF-8");
			response.setContentType("application/force-download");
			response.setHeader("Content-Disposition",
					"attachment;filename=" + URLEncoder.encode(exportFileName, "utf-8"));
			outputStream = response.getOutputStream();
			outputStream.write(dataByte);
			outputStream.flush();
			outputStream.close();
		} catch (IOException e) {
			log.warn("download-files getDownloadFile ex1.", e);
		} catch (Exception e) {
			log.error("download-files getDownloadFile ex.", e);
		} finally {
			try {
				if (inputStream != null) {
					inputStream.close();
				}
				if (outputStream != null) {
					outputStream.close();
				}
			} catch (IOException e) {
				log.warn("download-files getDownloadFile ex1.", e);
			}
		}
	}
	
	/**
     * 导出data到本地文件
     * @param filePath 文件名
     * @param header 表格头
     * @param dataList 数据列表
     * @param response 响应流
     * @param isMerge 是否合并单元格
     * @throws Exception
     */
    public static <T> byte[] exportWorkBookData(String filePath, Map<String, String> header, List<T> dataList) throws Exception {
        return exportWorkBookData(filePath, null, header, dataList, false, MAX_MERGE_VALUE);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, Map<String, String> header, List<T> dataList, boolean isMerge) throws Exception {
       return exportWorkBookData(filePath, null, header, dataList, isMerge, MAX_MERGE_VALUE);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, Map<String, String> header, List<T> dataList, boolean isMerge, WriteHandler writeHandler) throws Exception {
        return exportWorkBookData(filePath, null, header, dataList, isMerge, MAX_MERGE_VALUE, writeHandler);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, String name, Map<String, String> header, List<T> dataList,boolean isMerge) throws Exception {
    	 return exportWorkBookData(filePath, name, header, dataList, isMerge, MAX_MERGE_VALUE);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, String name, Map<String, String> header, List<T> dataList,boolean isMerge, WriteHandler writeHandler) throws Exception {
   	 return exportWorkBookData(filePath, name, header, dataList, isMerge, MAX_MERGE_VALUE, writeHandler);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, Map<String, String> header, List<T> dataList, boolean isMerge, int mergeCol) throws Exception {
        return exportWorkBookData(filePath, null, header, dataList, isMerge, mergeCol);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, Map<String, String> header, List<T> dataList, boolean isMerge, int mergeCol, WriteHandler writeHandler) throws Exception {
        return exportWorkBookData(filePath, null, header, dataList, isMerge, mergeCol, writeHandler);
    }
    
    public static ByteArrayInputStream toByteArrayInputStream(OutputStream out) throws Exception {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        baos = (ByteArrayOutputStream) out;
        final ByteArrayInputStream byteStream = new ByteArrayInputStream(baos.toByteArray());
        return byteStream;
    }
    
	public static <T> byte[] exportWorkBookData(String filePath, String name, Map<String, String> header, List<T> dataList,boolean isMerge, int mergeCol) throws Exception {
		 return exportWorkBookData(filePath, name, header, dataList, isMerge, mergeCol, null);
	}
	 
	 public static <T> byte[] exportWorkBookData(String filePath, String name, Map<String, String> header, List<T> dataList,boolean isMerge, int mergeCol, WriteHandler writeHandler) throws Exception {
		 List<Map<String, String>> headers = Lists.newArrayList();
		 List<String> names = Lists.newArrayList();
		 if(name == null) {
			 
			 if(filePath.lastIndexOf("/")>0) {
				 if(filePath.lastIndexOf(".")>0) {
			        name = filePath.substring(filePath.lastIndexOf("/")+1);
			     } else {
					 name = filePath;
				 }
			 } else {
				 if(filePath.lastIndexOf(".")>0) {
				    name = filePath.substring(0,filePath.lastIndexOf("."));
				 } else {
					 name = filePath;
				 }
			 }
		 }
		 names.add(name);
		 List<List<T>> dataLists = Lists.newArrayList();
		 headers.add(header);
		 dataLists.add(dataList);
		 return ExcelUtil1.exportWorkBookData(filePath, names, headers, dataLists, isMerge, mergeCol, writeHandler);
	}
	    
	/**
	 * 导出data到本地文件
	 * 
	 * @param filePath
	 *            文件名
	 * @param name
	 *            sheet名称
	 * @param header
	 *            表格头
	 * @param dataList
	 *            数据列表
	 * @param response
	 *            响应流
	 * @param isMerge
	 *            是否合并单元格
	 * @throws Exception
	 */
	private static <T> byte[] exportWorkBookData(String filePath, List<String> names, List<Map<String, String>> headers,
			List<List<T>> dataLists, boolean isMerge, int mergeCol, WriteHandler writeHandler) throws Exception {
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		HorizontalCellStyleStrategy horizontalCellStyleStrategy = getCellStyle(); 
		ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream);
		SimpleRowHeightStyleStrategy simpleRowHeightStyleStrategy = getRowStyle();
		excelWriterBuilder.registerWriteHandler(simpleRowHeightStyleStrategy);
		
		excelWriterBuilder.registerWriteHandler(horizontalCellStyleStrategy);
		excelWriterBuilder.registerConverter(new CusDateStringConverter());
		if (writeHandler != null) {
			excelWriterBuilder.registerWriteHandler(writeHandler);
		}
		if(isMerge) {
		   CustomCellWriteHandler customCellWriteHandler = new CustomCellWriteHandler(mergeCol);
		   excelWriterBuilder.registerWriteHandler(customCellWriteHandler);
		}
		ExcelWriter write = excelWriterBuilder.build();
		for (int i = 0; i < headers.size(); i++) {
			Map<String, String> header = headers.get(i);

			String sheetName = "sheet"+(i+1);
			if (names != null && !names.isEmpty()) {
				sheetName =names.get(i);
			}
			List<List<String>> headerList = Lists.newArrayList();
			List<List<Object>> data = Lists.newArrayList();
			for (Map.Entry<String, String> entry : header.entrySet()) {
				String value = entry.getValue();
				headerList.add(Arrays.asList(value));
			}
			List<T> dataList = dataLists.get(i);
			for (T t : dataList) {
				List<Object> list = Lists.newArrayList();
				for (Map.Entry<String, String> entry : header.entrySet()) {
					String key = entry.getKey();
					Object dataValue = getFieldValueByName(key, t);
					list.add(dataValue);
				}
				data.add(list);
			}
			if (filePath.toLowerCase().endsWith(xls)) {
				excelWriterBuilder.excelType(ExcelTypeEnum.XLS);
			} else {
				excelWriterBuilder.excelType(ExcelTypeEnum.XLSX);
			}

			// if (!Objects.isNull(writeHandler)) {
			// excelWriterBuilder.registerWriteHandler(writeHandler);
			// }
			// excelWriterBuilder.head(headerList);
			// excelWriterBuilder.sheet(sheetName).doWrite(data);

			WriteSheet writeSheet1 = EasyExcel.writerSheet(i, sheetName).head(headerList).build();
			write.write(data, writeSheet1);
		}
		write.finish();

		// 返回字节流
		byte[] bytes = outputStream.toByteArray();
		OutputStream stream = null;
		try {
			if (filePath.indexOf("/") >= 0) {
				File file = new File(filePath);

				stream = new FileOutputStream(file);

				stream.write(bytes);
				stream.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stream != null) {
					stream.close();
				}
				if (outputStream != null) {
					outputStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		return bytes;
	}
	
	public static  <T> byte[] exportWorkBookData(String filePath, List<T> data, Class<?> model) {
		return exportWorkBookData(filePath, null, data,model, null);
	}
	
	public static  <T> byte[] exportWorkBookData(String filePath, String sheetName,
			List<T> data, Class<?> model) {
		return exportWorkBookData(filePath, sheetName, data,model, null);
	}
	
	public static  <T> byte[] exportWorkBookData(String filePath, List<T> data, Class<?> model, WriteHandler writeHandler) {
		return exportWorkBookData(filePath, null, data,model, writeHandler);
	}
	
	public static  <T> byte[] exportWorkBookData(String filePath, String sheetName,
			List<T> data, Class<?> model, WriteHandler writeHandler){
		 List<String> sheetNames = Lists.newArrayList();
		 if(sheetName != null && !sheetName.isEmpty()) {
			 sheetNames.add(sheetName);
		 }
		 List<List<T>> datas = Lists.newArrayList();
		 datas.add(data);
		 List<Class<?>> models = Lists.newArrayList();
		 models.add(model);
		 
		return ExcelUtil1.exportWorkBookData(filePath, sheetNames, datas, models, writeHandler);
	}
	
	public static  <T> byte[] exportWorkBookData(String filePath, List<String> sheetNames,
			List<List<T>> datas, List<Class<?>> models) {
		return ExcelUtil1.exportWorkBookData(filePath, sheetNames, datas, models, null);
	}
	
	public static  <T> byte[] exportWorkBookData(String filePath, List<String> sheetNames,
			List<List<T>> datas, List<Class<?>> models, WriteHandler writeHandler) {
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		ExcelTypeEnum excelTypeEnum  = ExcelTypeEnum.XLSX;
		if (filePath.toLowerCase().endsWith(xls)) {
			excelTypeEnum=ExcelTypeEnum.XLS;
		}
		HorizontalCellStyleStrategy horizontalCellStyleStrategy = getCellStyle();
		ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream).excelType(excelTypeEnum);
		// 简单的行高策略：头行高30，内容行高15
		SimpleRowHeightStyleStrategy simpleRowHeightStyleStrategy =  getRowStyle();
		excelWriterBuilder.registerWriteHandler(simpleRowHeightStyleStrategy);
		
		excelWriterBuilder.registerWriteHandler(horizontalCellStyleStrategy);

		if (writeHandler != null) {
			excelWriterBuilder.registerWriteHandler(writeHandler);
		}
		excelWriterBuilder.registerConverter(new CusDateStringConverter());
        // 创建sheet
		ExcelWriter excelWrite = excelWriterBuilder.build();
		for(int i=0;i <datas.size();i++) {
			String sheetName = "sheet"+(i+1);
			List<T> data = datas.get(i);
			Class<?> model = models.get(i);
			if (sheetNames == null || sheetNames.isEmpty()) {
				if (filePath.lastIndexOf("/") > 0) {
					if (filePath.lastIndexOf(".") > 0) {
						sheetName = filePath.substring(filePath.lastIndexOf("/") + 1)+(i+1);
					} else {
						sheetName = filePath+(i+1);
					}
				} else {
					sheetName = filePath+(i+1);
				}
			} else {
				sheetName = sheetNames.get(i);
			}
			WriteSheet writeSheet1 = EasyExcel.writerSheet(i, sheetName).head(model).build();
			
			excelWrite.write(data, writeSheet1);
		}
		excelWrite.finish();
		
		// 返回字节流
		byte[] bytes = outputStream.toByteArray();
		OutputStream stream = null;
		try {
			if (filePath.indexOf("/") >= 0) {
				File file = new File(filePath);

				stream = new FileOutputStream(file);

				stream.write(bytes);
				stream.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stream != null) {
					stream.close();
				}
				if (outputStream != null) {
					outputStream.close();

				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return bytes;
	}
	
	public static  <T> BatchExcelData createBatchWorkBookData(String filePath, Map<String, String> header
			) throws Exception {
		BatchExcelData batchExcelData = new BatchExcelData (filePath, header, false, MAX_MERGE_VALUE);
		return batchExcelData;
	}
	
	public static  <T> BatchExcelData createBatchWorkBookData(String filePath, Map<String, String> header, 
			boolean isMerge) throws Exception {
		BatchExcelData batchExcelData = new BatchExcelData (filePath,header, isMerge, MAX_MERGE_VALUE);
		return batchExcelData;
	}
	
	public static  <T> BatchExcelData createBatchWorkBookData(String filePath, Map<String, String> header, 
			boolean isMerge, int mergeCol) throws Exception {
		BatchExcelData batchExcelData = new BatchExcelData (filePath,header, isMerge, mergeCol);
		return batchExcelData;
	}

	public static class BatchExcelData {
		String excelFile = null;
		OutputStream out = null;
		ExcelWriterBuilder excelWriterBuilder = null;
		ExcelWriter writer = null;

		// 动态添加表头，适用一些表头动态变化的场景
		WriteSheet sheet1 = null;
		Map<String, String> header;

		public BatchExcelData(String filePath, Map<String, String> header, boolean isMerge, int mergeCol) {
			try {
				this.excelFile = filePath;
				this.out = new ByteArrayOutputStream();
				this.excelWriterBuilder = EasyExcel.write(out);
				this.header = header;

				HorizontalCellStyleStrategy horizontalCellStyleStrategy = getCellStyle();
				
				SimpleRowHeightStyleStrategy simpleRowHeightStyleStrategy = getRowStyle();
				excelWriterBuilder.registerWriteHandler(simpleRowHeightStyleStrategy);
				
				excelWriterBuilder.registerWriteHandler(horizontalCellStyleStrategy);
				excelWriterBuilder.registerConverter(new CusDateStringConverter());
				if (filePath.toLowerCase().endsWith(xls)) {
					excelWriterBuilder.excelType(ExcelTypeEnum.XLS);
				} else {
					excelWriterBuilder.excelType(ExcelTypeEnum.XLSX);
				}
				this.writer = excelWriterBuilder.build();
				// 动态添加表头，普通实体创建excel
				String sheetName = "sheet1";
				if(filePath.lastIndexOf("/")>0) {
					sheetName = filePath.substring(filePath.lastIndexOf("/")+1);
				} else {
					 sheetName = filePath.substring(0,filePath.lastIndexOf("."));
				}
				this.sheet1 = EasyExcel.writerSheet(0, sheetName).build();

				// 创建一个表格，用于 Sheet 中使用
				WriteTable table1 = EasyExcel.writerTable(0).build();

				// 无注解的模式，动态添加表头
				List<List<String>> headerList = Lists.newArrayList();
				for (Map.Entry<String, String> entry : header.entrySet()) {
					String value = entry.getValue();
					headerList.add(Arrays.asList(value));
				}
				table1.setHead(headerList);

				// 写数据
				writer.write(new ArrayList<>(), sheet1, table1);

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		public <T> void write(List<T> dataList) {
			List<List<Object>> data = Lists.newArrayList();
			for (Object t : dataList) {
				List<Object> list = Lists.newArrayList();
				for (Map.Entry<String, String> entry : header.entrySet()) {
					String key = entry.getKey();
					Object dataValue = getFieldValueByName(key, t);
					list.add(dataValue);
				}
				data.add(list);
			}
			WriteTable tableX = EasyExcel.writerTable(0).build();
			// 每次从sheet的第几行开始写入
			writer.write(data, sheet1, tableX);
		}

		public byte[] flash() {
			// 将上下文中的最终 outputStream 写入到指定文件中
			writer.finish();
			byte[] bytes = ((ByteArrayOutputStream) out).toByteArray();
			
			return bytes;
		}

		public void close() {
			try {
				OutputStream stream = null;
				// 将上下文中的最终 outputStream 写入到指定文件中
				writer.finish();
				// 关闭流
				out.close();
				if (excelFile.indexOf("/") >= 0) {
					File file = new File(excelFile);
					byte[] bytes = ((ByteArrayOutputStream) out).toByteArray();
					stream = new FileOutputStream(file);

					stream.write(bytes);
					stream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

    /**
   * 导出data到本地文件,多个sheet
   * @param filePath 文件名
   * @param header 表格头
   * @param dataList 数据列表
   * @param response 响应流
   * @param names sheet名称
   * @param isMerge 是否合并单元格
   * @throws Exception
   */
    public static <T> byte[] exportWorkBookData(String filePath, List<Map<String, String>> headers, List<List<T>> dataLists) throws Exception {
        return exportWorkBookData(filePath, null, headers, dataLists, false, MAX_MERGE_VALUE, null);
    }

    public static <T> byte[] exportWorkBookData(String filePath, List<Map<String, String>> headers, List<List<T>> dataLists, boolean isMerge) throws Exception {
      return exportWorkBookData(filePath, null, headers, dataLists, isMerge, MAX_MERGE_VALUE, null);
    }

    public static <T> byte[] exportWorkBookData(String filePath,List<String> names, List<Map<String, String>> headers, List<List<T>> dataLists, boolean isMerge) throws Exception {
  	   return exportWorkBookData(filePath, names, headers, dataLists, isMerge, MAX_MERGE_VALUE, null);
    }

    public static <T> byte[] exportWorkBookData(String filePath, List<Map<String, String>> headers, List<List<T>> dataLists, boolean isMerge, int mergeCol) throws Exception {
        return exportWorkBookData(filePath, null, headers, dataLists, isMerge, mergeCol, null);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, List<Map<String, String>> headers, List<List<T>> dataLists, WriteHandler writeHandler) throws Exception {
        return exportWorkBookData(filePath, null, headers, dataLists, false, MAX_MERGE_VALUE, writeHandler);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, List<Map<String, String>> headers, List<List<T>> dataLists, boolean isMerge, WriteHandler writeHandler) throws Exception {
        return exportWorkBookData(filePath, null, headers, dataLists, isMerge, MAX_MERGE_VALUE, writeHandler);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath,List<String> names, List<Map<String, String>> headers, List<List<T>> dataLists, boolean isMerge, WriteHandler writeHandler) throws Exception {
   	   return exportWorkBookData(filePath, names, headers, dataLists, isMerge, MAX_MERGE_VALUE, writeHandler);
    }
    
    public static <T> byte[] exportWorkBookData(String filePath, List<Map<String, String>> headers, List<List<T>> dataLists, boolean isMerge, int mergeCol, WriteHandler writeHandler) throws Exception {
        return exportWorkBookData(filePath, null, headers, dataLists, isMerge, mergeCol, writeHandler);
    }
	
    private static Object getFieldValueByName(String fieldName, Object modle) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = modle.getClass().getMethod(getter, new Class[] {});
            Object value = method.invoke(modle, new Object[] {});
            return value;
        } catch (Exception e) {
            return null;
        }
    }
    
    public static List<?> getWorkBookData(String fileName, Class<?> clazz) {
    	ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(fileName, excelListener).sheet().doRead();

        return excelListener.getMapData();
    }

    public static List<?> getWorkBookData(InputStream inputStream, Class<?> clazz) {
        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(inputStream, clazz, excelListener).sheet().doRead();
        return excelListener.getExcelData();
    }
    
    public static List<?> getWorkBookData(InputStream inputStream, Class<?> clazz,int sheetNo) {
        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(inputStream, clazz, excelListener).sheet(sheetNo).doRead();
        return excelListener.getExcelData();
    }
    public static List<?> getWorkBookData(byte[] buf, Class<?> clazz,int sheetNo) {
        ExcelListener excelListener = new ExcelListener();
        InputStream inputStream = new ByteArrayInputStream(buf);
        EasyExcel.read(inputStream, clazz, excelListener).sheet(sheetNo).doRead();
        return excelListener.getExcelData();
    }
    
    public static  List<Map<String, String>>  getWorkBookData(String fileName) {
    	ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(fileName, excelListener).sheet().doRead();

        return excelListener.getMapData();
    }
    
    public static List<Map<String, String>> getWorkBookData(InputStream inputStream) {
        ExcelListener excelListener = new ExcelListener();
        EasyExcel.read(inputStream, excelListener).sheet().doRead();
        return excelListener.getMapData();
    }

    public static List<Map<String, String>> getWorkBookData(byte[] buf) {
        ExcelListener excelListener = new ExcelListener();
        try {
        	InputStream inputStream = new ByteArrayInputStream(buf);

            EasyExcel.read(inputStream, excelListener).sheet().doRead();
			inputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
        return excelListener.getMapData();
    }
    
    public static List<Map<String, String>> getWorkBookData(MultipartFile excelFile) {
    	 //获得文件名
        String fileName = excelFile.getOriginalFilename();
        ExcelListener excelListener = new ExcelListener();
        try {
            //获取excel文件的io流
            InputStream inputStream = excelFile.getInputStream();// 读取input流

            EasyExcel.read(inputStream, excelListener).sheet().doRead();
			inputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
        return excelListener.getMapData();
    }

    private static boolean isNumber(String value) {
    	if(value == null || value.isEmpty()) {
    		return false;
    	}
        String pattern = "(-)?[0-9]+(.[0-9]+)?";  
        Pattern p = Pattern.compile(pattern); 
        return p.matcher(value).matches();
    }
    /**
     * 创建我的cell  策略
     */
    private static HorizontalCellStyleStrategy getCellStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        // 字体
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setWrapped(true);
        
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置内容靠中对齐
//        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        return horizontalCellStyleStrategy;
    }
    
    private static SimpleRowHeightStyleStrategy getRowStyle() {
    	SimpleRowHeightStyleStrategy style = new SimpleRowHeightStyleStrategy((short)43,(short)15);
    	return style;
    }

    public static class ExcelListener extends AnalysisEventListener<Object> {
        private final List<Object> rows = new ArrayList<>();
        Map<Object, Object> header = new HashMap<Object, Object> ();
        @Override
        public void invoke(Object object, AnalysisContext context) {
            rows.add(object);
        }

        @Override
        public void invokeHeadMap(Map headMap, AnalysisContext context) {
        	 this.header = headMap;
             System.out.println("headMap: " + headMap);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
        	log.info("doAfterAllAnalysed read excel data total rows:{} end.", rows.size());
        }

        public List<Object> getExcelData() {
            return rows;
        }
        
		public List<Map<String, String>> getMapData() {
			List<Map<String, String>> list = Lists.newArrayList();
			for (Object row : rows) {
				if (row instanceof Map) {
					Map<Object, Object> objectMap = (Map<Object, Object>) row;

					Map<String, String> map = Maps.newConcurrentMap();
					for (Map.Entry a : objectMap.entrySet()) {
						Object key = a.getKey();
						Object value = a.getValue();
						String keyname = header.get(key).toString();
						String keyvalue = String.valueOf(value);
						map.put(keyname, keyvalue);
					}
					list.add(map);
				}
			}
			return list;
		}
    }
    
    
    
    public static class CusDateStringConverter implements Converter<Date> {
    	public static String PATTERN_DEFAULT = "yyyy-MM-dd HH:mm:ss";
        @Override
        public Class supportJavaTypeKey() {
            return Date.class;
        }

        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return CellDataTypeEnum.STRING;
        }

        @Override
        public Date convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration) throws ParseException {
            if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
                return DateUtils.parseDate(cellData.getStringValue(), null);
            } else {
                return DateUtils.parseDate(cellData.getStringValue(),
                    contentProperty.getDateTimeFormatProperty().getFormat());
            }
        }

        @Override
        public WriteCellData<?> convertToExcelData(Date value, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration) {
        	String s = "";
        	if(value !=null ) {
        	   s=DateUtils.format(value, PATTERN_DEFAULT);
        	   s = s.replace(" 00:00:00", "");
        	}
            if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
                return new WriteCellData<>(s);
            } else {
                return new WriteCellData<>(s);
            }
        }
    }
    
    
    public static class CustomCellWriteHandler implements CellWriteHandler {

        private static final Logger LOGGER = LoggerFactory.getLogger(CustomCellWriteHandler.class);

        public int merColumn = 2;
        public Map<String,String> merMap= new HashMap<String,String>(); 
        
        public CustomCellWriteHandler() {
        	merMap= new HashMap<String,String>(); 
        }
        public CustomCellWriteHandler(int merColumn) {
        	this.merColumn = merColumn;
        	merMap= new HashMap<String,String>(); 
        }
        
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                     Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

        }

        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                    Head head, Integer relativeRowIndex, Boolean isHead) {

        }

        @Override
        public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

        }

        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
        		List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            // 这里可以对cell进行任何操作
            int rowIndex = cell.getRowIndex();
            int columnIndex = cell.getColumnIndex();
            if(columnIndex >= merColumn){
                return;
            }
            mergeWithPrevRow(writeSheetHolder,cell,rowIndex,columnIndex);

        }


        /**
         * 当前单元格向上合并
         *
         * @param writeSheetHolder
         * @param cell             当前单元格
         * @param curRowIndex      当前行
         * @param curColIndex      当前列
         */
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
            //拿到当前单元格数据
            Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
            if(curRowIndex <= 1){
                return;
            }
            Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
            //拿到上一列的单元格数据
            Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
            if(curColIndex>0 && merMap.get(curRowIndex+":"+(curColIndex-1))==null) {
               return;	
            }
            
            // 将当前单元格数据与上一个单元格数据比较
            if (preData.equals(curData)) {
                Sheet sheet = writeSheetHolder.getSheet();
                List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
                boolean isMerged = false;
                for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                    CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                    // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                    if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                        sheet.removeMergedRegion(i);
                        cellRangeAddr.setLastRow(curRowIndex);
                        sheet.addMergedRegion(cellRangeAddr);
                        isMerged = true;
                    }
                }
                // 若上一个单元格未被合并，则新增合并单元
                if (!isMerged) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                    sheet.addMergedRegion(cellRangeAddress);                    
                }
                merMap.put(curRowIndex+":"+curColIndex, curRowIndex+":"+curColIndex);
            }
        }

    }
    
    public static class ColorFormat{
    	int cellIndex;
    	int cellColor;
    	Long target = 0L;
    	String rule = "<";

    	public ColorFormat(int index) {
    		this.cellIndex = index;
        }
    	public ColorFormat(int index, int color) {
    		this.cellIndex = index;
    		this.cellColor = color;
        }
    	public ColorFormat(int index, int color, long targe) {
    		this.cellIndex = index;
    		this.cellColor = color;
    		this.target = targe;
    	}
    	public ColorFormat(int index, int color,long targe, String rule) {
    		this.cellIndex = index;
    		this.cellColor = color;
    		this.target = targe;
    		this.rule = rule;
    	}
    }
    
    public static class ColorHandler implements CellWriteHandler {
    	int cellIndex = 0;
    	int cellColor = IndexedColors.RED.index;

    	Map<Integer, ColorFormat> format = new HashMap<>();

        public ColorHandler(int[] indexs) {
        	for(int index : indexs) {
        		 ColorFormat ColorFormat = new ColorFormat(index, cellColor);
          	     format.put(Integer.valueOf(index), ColorFormat);
        	}
        }
        
        public ColorHandler(List<ColorFormat> indexs) {
        	for(ColorFormat index : indexs) {
          	     format.put(index.cellIndex, index);
        	}
        }
        
        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            // 当前事件会在 数据设置到poi的cell里面才会回调
            // 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not true
            if (BooleanUtils.isNotTrue(context.getHead())) {
                // 第一个单元格
                // 只要不是头 一定会有数据 当然fill的情况 可能要context.getCellDataList() ,这个需要看模板，因为一个单元格会有多个 WriteCellData
                WriteCellData<?> cellData = context.getFirstCellData();
                int sheetNo = context.getWriteSheetHolder().getSheetNo();
                if(sheetNo > 0) {
                	return;
                }
                DataFormatter dataFormatter = new DataFormatter();
                Cell cell = context.getCell();
                String stringCellValue = dataFormatter.formatCellValue(cell);
                if ( !StringUtils.isEmpty(stringCellValue)) {
                    int i = cell.getColumnIndex();
                    ColorFormat format1 = format.get(Integer.valueOf(i));
					if (format1 != null && isDouble(stringCellValue)) {
						double cellValue = Double.valueOf(stringCellValue);
						if (cellValue < format1.target) {
							// 这里需要去cellData 获取样式,getOrCreateStyle 用于返回一个样式，如果为空，则创建一个后返回
							WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
							WriteFont writeFont = new WriteFont();
							// 设置字体颜色
							writeFont.setColor(IndexedColors.RED.index);
							writeCellStyle.setWriteFont(writeFont);
						}
					}
                }
            }
        }
    }
    
    public static ColorHandler getCellColor(int[] indexs) {
        ColorHandler h = new ColorHandler(indexs);
        return h;
    }
    
    private static boolean isInteger(String str) {
    	if (null == str || "".equals(str)) {
    		return false;
    	}
    	Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
    	return pattern.matcher(str).matches();
    }

    private static boolean isDouble(String str) {
    	if (null == str || "".equals(str)) {
    		return false;
    	}
    	Pattern pattern = Pattern.compile("^[-\\+]?\\d*[.]?\\d+$");
    	return pattern.matcher(str).matches();
    }
    
    public static byte[] inputStreamToByteArray(InputStream inputStream) throws IOException {
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int bytesRead;
        while ((bytesRead = inputStream.read(buffer)) != -1) {
            byteArrayOutputStream.write(buffer, 0, bytesRead);
        }
        return byteArrayOutputStream.toByteArray();
    }
}

